package com.qc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.qc.common.Constants;
import com.qc.common.DBHandler;
import com.qc.common.DBUtil;
import com.qc.common.DateUtil;
import com.qc.common.StringUtil;
import com.qc.common.Util;
import com.qc.dao.UserDao;
import com.qc.entity.BannerEntity;
import com.qc.entity.BossEntityVo;
import com.qc.entity.EducationEntity;
import com.qc.entity.HeightEntity;
import com.qc.entity.InterestUserEntity;
import com.qc.entity.OccupationEntity;
import com.qc.entity.OrderCommentEntity;
import com.qc.entity.PhoneVersionEntity;
import com.qc.entity.PointEntity;
import com.qc.entity.ReportEntity;
import com.qc.entity.SecScheduleCommentEntity;
import com.qc.entity.SecScheduleReplyCommentEntity;
import com.qc.entity.SecretaryEntity;
import com.qc.entity.SecretaryVideoEntity;
import com.qc.entity.SkillTagEntity;
import com.qc.entity.UserAuthVerifyEntity;
import com.qc.entity.UserBossEntity;
import com.qc.entity.UserBossScheduleEntity;
import com.qc.entity.UserCashEntity;
import com.qc.entity.UserEntity;
import com.qc.entity.UserEntityVo;
import com.qc.entity.UserFeedBackEntity;
import com.qc.entity.UserMoneyLogEntity;
import com.qc.entity.UserSecScheduleEntity;
import com.qc.entity.UserSecretaryEntity;
import com.qc.entity.UserVerifyEntityVo;
import com.qc.entity.search.UserBossSearchEntity;
import com.qc.entity.search.UserSecSearchEntity;

@SuppressWarnings("rawtypes")
@Repository
public class UserDaoImpl implements UserDao {
	
	@Resource
	private DataSource dataSource;
	
	@Resource(name = "jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public long register(UserEntity entity) throws SQLException {
		DBHandler dbHandler = new DBHandler();
		
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getUserName());
		l.add(entity.getUserPwd());
		l.add(entity.getMobile());
		l.add(entity.getDeviceId());
		l.add(entity.getRefSource());
		l.add(entity.getCreatedTime());
		l.add(entity.getNickname());
		long i = dbHandler.update(DBUtil.getSql(Constants.USER_REGISTER), l, dataSource);
		//i: 表示增删改这一条数据的自增长主键ID
		
		//添加秘书信息
		List<Object> l1 = new ArrayList<Object>();
		l1.add(i);
		l1.add(DateUtil.getCurrentSysTimestamp());
		
		long i1 = dbHandler.update(DBUtil.getSql(Constants.SAVE_USER_SECRETARY), l1, dataSource);
		
		//添加BOSS信息
		List<Object> l2 = new ArrayList<Object>();
		l2.add(i);
		l2.add(DateUtil.getCurrentSysTimestamp());
		
		long i2 = dbHandler.update(DBUtil.getSql(Constants.SAVE_USER_BOSS), l2, dataSource);
		
		return i;
	}

	@Override
	public UserEntity getUserName(String userName) {
		
		String sql = "select * from t_user where user_name = " + userName;
		
		UserEntity userEntity = new UserEntity();
		
		jdbcTemplate.query(sql, new RowMapper<UserEntity>() {
            public UserEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
            
            	//id
            	userEntity.setUserId(rs.getLong("user_id"));
            	 //用户名
            	userEntity.setUserName(rs.getString("user_name"));
            	//密码
            	userEntity.setUserPwd(rs.getString("user_pwd"));
            	//余额
            	userEntity.setBalance(rs.getDouble("balance"));
            	//手机号
            	userEntity.setMobile(rs.getString("mobile"));
                //用户昵称
            	userEntity.setNickname(rs.getString("nickname"));
                //用户头像
            	userEntity.setAvatar(rs.getString("avatar"));
                //性别
            	userEntity.setSex(rs.getString("sex"));
                //真实名
            	userEntity.setRealName(rs.getString("real_name"));
            	//出生年月
            	userEntity.setUserAge(rs.getString("user_age"));
            	//工作年限
            	userEntity.setJobAge(rs.getString("job_age"));
            	//省
            	userEntity.setProvince(rs.getString("province"));
            	//市
            	userEntity.setCity(rs.getString("city"));
            	//用户token
            	userEntity.setToken(rs.getString("token"));
            	//关注数
            	userEntity.setFollowNumber(rs.getInt("follow_number"));
            	//浏览数
            	userEntity.setBrowseNumber(rs.getInt("browse_number"));
            	//是否接单状态
            	userEntity.setOrdersState(rs.getInt("orders_state"));
            	
            	
            	return userEntity;
            }
		});
		
		return userEntity;
	}
	
	@Override
	public String getMobileCode(String mobile) {
		DBHandler dbHandler = new DBHandler();
		
		List<Object> l = new ArrayList<Object>();
		l.add(mobile);
		dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.MOBILE_CODE), l, dataSource);
		try {
			if (dbHandler.rs.next()) { 
				return dbHandler.rs.getString("code"); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long saveCode(String mobile, String code) {
		DBHandler dbHandler = new DBHandler();
		
		List<Object> l = new ArrayList<Object>();
		l.add(mobile);
		l.add(code);
		l.add(DateUtil.getCurrentSysTimestamp());
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.SEND_CODE), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public UserEntity userLogin(String mobile, String pwd) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(mobile);
		l.add(pwd);
		try {
			dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.USER_LOGIN), l, dataSource);
			if(dbHandler.rs.next()){

				UserEntity entity = UserEntity.getInstance(dbHandler.rs);
				return entity;
			}
//			return (UserEntity) FormatObjToBean.parseDataSourceForObj(Constants.USER_ENTITY, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long forgetPwd(String mobile, String pwd) {
		DBHandler dbHandler = new DBHandler();
		
		List<Object> l = new ArrayList<Object>();
		l.add(pwd);
		l.add(mobile);
		long i = 0;
		try {
			
			i = dbHandler.update(DBUtil.getSql(Constants.FORGET_PWD), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public long updateCodeByMobile(String mobile, String code) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(code);
		l.add(mobile);
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_CODE), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public long updateRole(int userRole, long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(userRole);
		l.add(userId);
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_ROLE), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public long updateAvatar(String avatarName, long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(avatarName);
		l.add(userId);
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_AVATAR), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public long updateUserInfo(UserEntity entity) throws SQLException {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getNickname());
		l.add(entity.getSex());
		l.add(entity.getJobAge());
		l.add(entity.getUserAge());
		l.add(entity.getRealName());
		l.add(entity.getUserId());
		
		long i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_USERINFO), l, dataSource);
		return i;
	}

	@Override
	public long updateBindMobile(String mobile, long userId) throws SQLException {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(mobile);
		l.add(userId);

		long i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_BIND_MOBILE), l, dataSource);
			
		
		return i;
	}

	@Override
	public UserEntity getUserInfoByUserId(long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(userId);
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.USER_INFO_BYUSERID), l, dataSource);
			if(dbHandler.rs.next()){

				UserEntity entity = UserEntity.getInstance(dbHandler.rs);
				return entity;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
		
		
	}

	@Override
	public long updateUserPwd(UserEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getNewPwd());
		l.add(entity.getUserId());

		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_USER_PWD), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
	}

	@Override
	public long saveFeedBack(UserFeedBackEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getUserId());
		l.add(entity.getFbContent());
		l.add(entity.getContactType());

		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_FEED_BACK), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
	}

	@Override
	public long saveSecretarySchedule(UserSecScheduleEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getUserId());
		l.add(entity.getTitle());
		l.add(entity.getCoverUrl());
		l.add(entity.getShopMark());
		l.add(entity.getShopProvince());
		l.add(entity.getShopCity());
		l.add(entity.getShopDistrict());
		l.add(entity.getShopAddress());
		l.add(entity.getShopLatitude());
		l.add(entity.getShopLongitude());
		l.add(entity.getTag());
		l.add(entity.getSsContent());
		l.add(entity.getCreatedTime());
		l.add(entity.getShopId());
		l.add(entity.getbCategoryOneId());
		l.add(entity.getbCategoryTwoId());
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_SECRETARY_SCHEDULE), l, dataSource);
			return i;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return i;
	}
	
	public int deleteSecretarySchedule(UserSecScheduleEntity entity) {
		
		String sql = "update t_secretary_schedule set if_del = 1 where ss_id = " + entity.getSsId() +  " and user_id =" + entity.getUserId();
		
		return jdbcTemplate.update(sql);
	}

	@Override
	public List<UserSecScheduleEntity> getSecScheduleByUserId(long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(userId);
		
		List<UserSecScheduleEntity> list = new ArrayList<UserSecScheduleEntity>();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_SECRETARY_MY_SCHEDULE_BY_USERID), l, dataSource);
			while(dbHandler.rs.next()){

				UserSecScheduleEntity entity = UserSecScheduleEntity.getInstance(dbHandler.rs);
				
				list.add(entity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public List<SkillTagEntity> getAllSkillTag() {

		List<Object> l = new ArrayList<Object>();
		
		List<SkillTagEntity> list = new ArrayList<SkillTagEntity>();
		DBHandler dbHandler = new DBHandler();
		try {
			
			
			dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.SELECT_ALL_SKILL_TAG), l, dataSource);
			while(dbHandler.rs.next()){

				SkillTagEntity entity = SkillTagEntity.getInstance(dbHandler.rs);
				
				list.add(entity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public List<UserSecScheduleEntity> getSecScheduleByCity(UserSecSearchEntity entity) {
		
		String sql = DBUtil.getSql(Constants.SELECT_SECRETARY_SCHEDULE_BY_CITY);
		StringBuilder sb = new StringBuilder();
		sb.append(sql);
		sb.append(" and 1 = 1");
		
		//性别
		if (StringUtil.isNotEmpty(entity.getSex())) {
			sb.append(" and u.sex = '"+entity.getSex()+"'");
		}
		//价格
		if (entity.getMinMoney() <= 0 && entity.getMaxMoney() <= 0) {
			
		} else {
			if (entity.getMaxMoney() <= 0) {
				sb.append(" and us.money_day >= "+entity.getMinMoney());
			} else {
				sb.append(" and us.money_day >= "+entity.getMinMoney()+" and us.money_day <= "+entity.getMaxMoney());
			}
			
			sb.append(" and u.orders_state = 1 ");
			
		}
		
		//技能标签
		if (StringUtil.isEmpty(entity.getSkillTag())) {
			sb.append(" and ss.tag like '%"+entity.getSkillTag()+"%'");
		}
		
		//一级分类
		if (0 != entity.getbCategoryOneId()) {
			sb.append(" and ss.b_category_one_id = '"+entity.getbCategoryOneId()+"'");
		}
		
		//二级分类
		if (0 != entity.getbCategoryTwoId()) {
			sb.append(" and ss.b_category_two_id = '"+entity.getbCategoryTwoId()+"'");
		}
		
		//是否接单
		if (1 == entity.getOrdersState()) {
			sb.append(" and u.orders_state = 0");
		}else if (entity.getOrdersState() == 2) {
			sb.append(" and u.orders_state = 1");
		}
		
		if(4 == entity.getSortType() || 5 == entity.getSortType()) {
			sb.append(" and u.orders_state = 1 ");
		}
		
		sb.append(" group by ss.ss_id");
		if (entity.getGrade() > 0) {
			sb.append(" having  grade >= "+entity.getGrade()+" and grade < "+(entity.getGrade()+1));
		}
		
		//排序类型，1/智能排序 2/星级最高 3/最新上线 4/价格最高 5/价格最低
		if (1 == entity.getSortType()) {
			sb.append(" order by ss.ss_id desc");
		} else if(2 == entity.getSortType()) {
			sb.append(" order by grade desc");
		} else if(4 == entity.getSortType()) {
			sb.append(" order by us.money_day desc");
		} else if(5 == entity.getSortType()) {
			sb.append(" order by us.money_day");
		} else {
			sb.append(" order by ss.ss_id desc");
		}
		sb.append(" limit ?,?");
		
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getTargetCity());
//		l.add(entity.getUserId());
		l.add((entity.getPageNo() - 1 ) * entity.getPageSize());
		l.add(entity.getPageSize());
		
		List<UserSecScheduleEntity> list = new ArrayList<UserSecScheduleEntity>();
		
		DBHandler dbHandler = new DBHandler();
		
		try {
			dbHandler.rs = dbHandler.query(sb.toString(), l, dataSource);
			while(dbHandler.rs.next()){

				UserSecScheduleEntity result = UserSecScheduleEntity.getInstance(dbHandler.rs);
				
				list.add(result);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long updateSecScheduleRead(long ssId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add((int)Math.round(Math.random()*(5-1)+1));//随机生成5-10之间的数字
		l.add(ssId);
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_SECRETARY_SCHEDULE_READ_COUNT), l, dataSource);
			return i;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return i;
	}

	@Override
	public long saveSecScheduleComment(SecScheduleCommentEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getSecScheduleId());
		l.add(entity.getUserId());
		l.add(entity.getUcContent());
		l.add(DateUtil.getCurrentSysTimestamp());
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_SECRETARY_SCHEDULE_COMMENT), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
	}

	@Override
	public long saveSecScheduleReplyComment(SecScheduleReplyCommentEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getSecCommentId());
		l.add(entity.getUserId());
		l.add(entity.getUcrContent());
		l.add(DateUtil.getCurrentSysTimestamp());
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_SECRETARY_SCHEDULE_REPLY_COMMENT), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
	}

	@Override
	public List<SecScheduleCommentEntity> getSecretaryCommentBySSID(long secScheduleId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(secScheduleId);
		
		List<SecScheduleCommentEntity> list = new ArrayList<SecScheduleCommentEntity>();
		SecScheduleCommentEntity tmpEntity = null;
		List<SecScheduleReplyCommentEntity> replyList = new ArrayList<SecScheduleReplyCommentEntity>();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_SECRETARY_SCHEDULE_COMMENT_BY_SSID), l, dataSource);
			while(dbHandler.rs.next()){
				SecScheduleCommentEntity result = SecScheduleCommentEntity.getInstance(dbHandler.rs);
				SecScheduleReplyCommentEntity replyEntity = SecScheduleReplyCommentEntity.getInstance(dbHandler.rs);
				if (tmpEntity == null) {
					tmpEntity =result;
					if (replyEntity.getUcrId() > 0) {
						replyList.add(replyEntity);
					}
					

					tmpEntity.setList(replyList);
					list.add(tmpEntity);
				} else {
					if (tmpEntity.getScId() == result.getScId()) {
						if (replyEntity.getUcrId() > 0) {
							replyList.add(replyEntity);
						}
						
					} else {

						//初始化 回复list
						replyList = new ArrayList<SecScheduleReplyCommentEntity>();
						
						tmpEntity = result;
						if (replyEntity.getUcrId() > 0) {
							replyList.add(replyEntity);
						}
						
						tmpEntity.setList(replyList);
						list.add(tmpEntity);

					}
				}

			}
			
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long updateSecretaryInfo(UserSecretaryEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getSecHeight());
		l.add(entity.getEducation());
		l.add(entity.getSpecialty());
		l.add(entity.getJob());
		l.add(entity.getTagIds());
		l.add(entity.getMoneyDay());
		l.add(entity.getJobArea());
		l.add(entity.getNationality());
		l.add(entity.getNation());
		l.add(entity.getBloodStyle());
		l.add(entity.getLiveProvince());
		l.add(entity.getLiveCity());
		l.add(entity.getSchool());
		l.add(entity.getIntro());
		l.add(entity.getSecFrontPhoto());
		l.add(entity.getSecFullPhoto());
		l.add(entity.getSecLivePhoto());
		l.add(entity.getHomeProvince());
		l.add(entity.getHomeCity());
		l.add(entity.getHeightPrice());
		l.add(entity.getEducationPrice());
		l.add(entity.getSpecialtyPrice());
		l.add(entity.getJobPrice());
		l.add(entity.getTagPrice());
		l.add(entity.getbName());
		l.add(entity.getUserId());
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_SECRETARY_INFO), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
	}

	@Override
	public UserSecretaryEntity getSecretaryInfoByUserId(long bossUserId, long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(bossUserId);
		l.add(userId);
		l.add(bossUserId);
		l.add(userId);
		l.add(userId);
		l.add(userId);
		
		UserSecretaryEntity entity = new UserSecretaryEntity();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_SECRETARY_BY_USERID), l, dataSource);
			if(dbHandler.rs.next()){

				entity = UserSecretaryEntity.getInstance(dbHandler.rs);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		
		//查询秘书发布
		List<Object> l1 = new ArrayList<Object>();
		l1.add(entity.getSecUserId());
		try {
			
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_SECRETARY_SCHEDULE_BY_USERID), l1, dataSource);
			while(dbHandler.rs.next()){

				UserSecScheduleEntity ssEntity = UserSecScheduleEntity.getInstance(dbHandler.rs);
				if (entity.getSsList() == null) {
					entity.setSsList(new ArrayList<UserSecScheduleEntity>());
				}
				entity.getSsList().add(ssEntity);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		
		try {
			//查询秘书的接单评价
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_SECRETARY_ORDER_COMMENT_BY_USERID_LIMIT), l1, dataSource);
			while(dbHandler.rs.next()){

				OrderCommentEntity ocEntity = OrderCommentEntity.getInstance(dbHandler.rs);
				if (entity.getOcList() == null) {
					entity.setOcList(new ArrayList<OrderCommentEntity>());
				}
				entity.getOcList().add(ocEntity);
			}
			return entity;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		
		
		return null;
	}

	@Override
	public long updateBossInfo(UserBossEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getCompany());
		l.add(entity.getDuty());
		l.add(entity.getIndustry());
		l.add(entity.getInterestTag());
		l.add(entity.getBossSign());
		l.add(entity.getSecFrontPhoto());
		l.add(entity.getSecFullPhoto());
		l.add(entity.getSecLivePhoto());
		l.add(entity.getUserId());
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_BOSS_INFO), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
	}

	@Override
	public UserBossEntity getBossInfoByUserId(long secUserId,long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(secUserId);
		l.add(userId);
		l.add(userId);
		l.add(secUserId);
		l.add(userId);
		UserBossEntity entity = new UserBossEntity();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_BOSS_BY_USERID), l, dataSource);
			if(dbHandler.rs.next()){

				entity = UserBossEntity.getInstance(dbHandler.rs);
				
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		
		try {
			//查询秘书的接单评价
			List<Object> l1 = new ArrayList<Object>();
			l1.add(userId);
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_BOSS_ORDER_COMMENT_BY_USERID), l1, dataSource);
			while(dbHandler.rs.next()){

				OrderCommentEntity ocEntity = OrderCommentEntity.getInstance(dbHandler.rs);
				if (entity.getOcList() == null) {
					entity.setOcList(new ArrayList<OrderCommentEntity>());
				}
				entity.getOcList().add(ocEntity);
			}
			
			return entity;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		
		
		return null;
	}

	@Override
	public long saveBossSchedule(UserBossScheduleEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getUserId());
		l.add(entity.getScheduleType());
		l.add(entity.getStartTime());
		l.add(entity.getEndTime());
		l.add(entity.getTargetProvince());
		l.add(entity.getTargetCity());
		l.add(entity.getPeopleNumber());
		l.add(entity.getDays());
		l.add(entity.getMoneyDay());
		l.add(entity.getDemand());
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_BOSS_SCHEDULE), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public List<UserBossScheduleEntity> getBossScheduleByUserId(long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(userId);
		
		List<UserBossScheduleEntity> list = new ArrayList<UserBossScheduleEntity>();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_BOSS_SCHEDULE_BY_USERID), l, dataSource);
			while(dbHandler.rs.next()){

				UserBossScheduleEntity entity = UserBossScheduleEntity.getInstance(dbHandler.rs);
				
				list.add(entity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public List<UserBossScheduleEntity> getBossScheduleByCity(UserBossSearchEntity entity) {
		DBHandler dbHandler = new DBHandler();
		String sql = DBUtil.getSql(Constants.SELECT_BOSS_SCHEDULE_BY_CITY);
		StringBuilder sb = new StringBuilder();
		sb.append(sql);
		sb.append(" and 1 = 1");
		//性别
		if (entity.getSex() != null && !entity.getSex().equals("")) {
			sb.append(" and u.sex = '"+entity.getSex()+"'");
		}
		//价格
		if (entity.getMinMoney() <= 0 && entity.getMaxMoney() <= 0) {
			
		} else {
			if (entity.getMaxMoney() <= 0) {
				sb.append(" and bs.money_day >= "+entity.getMinMoney());
			} else {
				sb.append(" and bs.money_day >= "+entity.getMinMoney()+" and bs.money_day <= "+entity.getMaxMoney());
			} 
		}
		
		//天数
		if (entity.getDays() > 0 && entity.getDays() < 8) {
			sb.append(" and bs.days = "+entity.getDays());
		} else if(entity.getDays() > 0 && entity.getDays() >=8) {
			sb.append(" and bs.days >= "+entity.getDays());
		}
		//人数
		if (entity.getPeopleNumber() > 0 && entity.getPeopleNumber() < 6) {
			sb.append(" and bs.people_number = "+entity.getPeopleNumber());
		} else if (entity.getPeopleNumber() > 0 && entity.getPeopleNumber() >= 6) {
			sb.append(" and bs.people_number = "+entity.getPeopleNumber());
		}
		
		//排序类型，1/智能排序 2/最新发布 3/价格最高 4/价格最低
		if (entity.getSortType() == 1) {
			sb.append(" order by bs.bs_id desc");
		} else if(entity.getSortType() == 2) {
			sb.append(" order by bs.bs_id desc");
		} else if(entity.getSortType() == 3) {
			sb.append(" order by bs.money_day desc");
		} else if(entity.getSortType() == 4) {
			sb.append(" order by bs.money_day");
		} else {
			sb.append(" order by bs.bs_id desc");
		}
		sb.append(" limit ?,?");
				
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getTargetCity());
		l.add(entity.getUserId());
		l.add((entity.getPageNo() - 1 ) * entity.getPageSize());
		l.add(entity.getPageSize());
		
		List<UserBossScheduleEntity> list = new ArrayList<UserBossScheduleEntity>();
		try {
			dbHandler.rs= dbHandler.query(sb.toString(), l, dataSource);
			while(dbHandler.rs.next()){

				UserBossScheduleEntity result = UserBossScheduleEntity.getInstance(dbHandler.rs);
				
				list.add(result);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long updateRegistrationId(String registrationId, long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(registrationId);
		l.add(userId);
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.MODIFY_REGISTRATIONID), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
		
	}

	@Override
	public long interestUser(InterestUserEntity entity) {
		DBHandler dbHandler = new DBHandler();
		// TODO Auto-generated method stub
		//
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getSenderUserId());
		l.add(entity.getReceiveUserId());
		l.add(entity.getReceiveUserRole());
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_INTEREST_USER), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public long cancelInterestUser(InterestUserEntity entity) {
		DBHandler dbHandler = new DBHandler();
		// TODO Auto-generated method stub
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getSenderUserId());
		l.add(entity.getReceiveUserId());
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.DELETE_CANCEL_INTEREST_USER), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public List<InterestUserEntity> getInterestUser(long receiveUserId, int type) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(receiveUserId);
		l.add(type);
		List<InterestUserEntity> list = new ArrayList<InterestUserEntity>();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_INTEREST_USER), l, dataSource);
			while(dbHandler.rs.next()){

				InterestUserEntity result = InterestUserEntity.getInstance(dbHandler.rs);
				
				list.add(result);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public List<OrderCommentEntity> getOrderCommentByUserId(long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(userId);
		List<OrderCommentEntity> list = new ArrayList<OrderCommentEntity>();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_SECRETARY_ORDER_COMMENT_BY_USERID), l, dataSource);
			while(dbHandler.rs.next()){

				OrderCommentEntity result = OrderCommentEntity.getInstance(dbHandler.rs);
				
				list.add(result);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public UserSecScheduleEntity getSecScheduleBySSId(long ssId, long userId) {
		DBHandler dbHandler = new DBHandler();
		// TODO Auto-generated method stub
		//SELECT_SECRETARY_SCHEDULE_BY_SSID
		List<Object> l = new ArrayList<Object>();
		l.add(ssId);
		UserSecScheduleEntity entity = new UserSecScheduleEntity();
		try {
			
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_SECRETARY_SCHEDULE_BY_SSID), l, dataSource);
			if(dbHandler.rs.next()){

				entity = UserSecScheduleEntity.getInstance(dbHandler.rs);
				entity.setShopId(dbHandler.rs.getLong("shop_id"));
				entity.setImage(dbHandler.rs.getString("shop_image"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		
		if (userId != 0) {
			try {
				l = new ArrayList<Object>();
				l.add(userId);
				l.add(entity.getUserId());
				dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_BOTH_IS_FOLLOW), l, dataSource);
				if (dbHandler.rs.next()) {
					entity.setIsFollowed(dbHandler.rs.getInt("isFollowed"));
				}
				
				return entity;
				
			} catch (Exception e) {
				e.printStackTrace();
			}  finally {
				dbHandler.closeAll();
			}
		}
		
		return entity;
	}

	@Override
	public long delSecScheduleComment(SecScheduleCommentEntity entity) {
		DBHandler dbHandler = new DBHandler();
		// TODO Auto-generated method stub
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getScId());
		l.add(entity.getUserId());
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.DELETE_SECRETARY_SCHEDULE_COMMENT), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public SecretaryVideoEntity getSecretaryVideoByUserId(long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(userId);

		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_SECRETARY_VIDEO_BY_USERID), l, dataSource);
			if(dbHandler.rs.next()){

				SecretaryVideoEntity entity = SecretaryVideoEntity.getInstance(dbHandler.rs);
				
				return entity;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long addSecretaryVideo(SecretaryVideoEntity entity) {
		DBHandler dbHandler = new DBHandler();
		// TODO Auto-generated method stub
		//ADD_SECRETARY_VIDEO
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getUserId());
		l.add(entity.getVideo());
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.ADD_SECRETARY_VIDEO), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public long updateSecretaryVideo(SecretaryVideoEntity entity) {
		DBHandler dbHandler = new DBHandler();
		// TODO Auto-generated method stub
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getVideo());
		l.add(entity.getVideoId());
		l.add(entity.getUserId());
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_SECRETARY_VIDEO), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public long updateSecretaryVideoRead(long userId) {
		DBHandler dbHandler = new DBHandler();
		// TODO Auto-generated method stub
		List<Object> l = new ArrayList<Object>();
		l.add((int)Math.round(Math.random()*(10-5)+5));//随机生成5-10之间的数字
		l.add(userId);
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_SECRETARY_VIDEO_READCOUNT), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public long addAuthVerify(UserAuthVerifyEntity entity) {
		DBHandler dbHandler = new DBHandler();
		// TODO Auto-generated method stub
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getUserId());
		l.add(entity.getvRealName());
		l.add(entity.getvFrontCard());
		l.add(entity.getvBackCard());
		l.add(entity.getvVideo());
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_USER_VERIFY), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public UserAuthVerifyEntity getAuthVerify(long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(userId);

		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.GET_USER_VERIFY), l, dataSource);
			if(dbHandler.rs.next()){

				UserAuthVerifyEntity entity = UserAuthVerifyEntity.getInstance(dbHandler.rs);
				
				return entity;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long saveTokenByUserId(long userId, String token) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(token);
		l.add(userId);
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_USER_TOKEN), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public UserEntity getUserInfoByPwdAndUserId(long userId, String pwd) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(userId);
		l.add(pwd);
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_USER_BY_USERID_AND_PWD), l, dataSource);
			if(dbHandler.rs.next()){

				UserEntity entity = UserEntity.getInstance(dbHandler.rs);
				return entity;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}
	
	@Override
    public int getListUserCount(String userName, String isAuth, String sex,String userRole) {
	    
	    String sql = " select count(*) from t_user u "
	        + " left join t_user_secretary s on s.user_id = u.user_id "
	        + " left join t_authentication v on v.user_id = u.user_id "
	        + " where 1 = 1 and u.if_del = '0'  ";
	    
	    if (StringUtil.isNotEmpty(userRole)) {
	        sql += " and u.user_role = '"+userRole+"' ";
	    }
	    //性别
        if (StringUtil.isNotEmpty(sex)) {
            sql += " and u.sex = '"+sex+"' ";
        }

        //认证
        if (StringUtil.isNotEmpty(isAuth)) {
            sql += " and v.type = "+isAuth;
        }
        
        //用户名查询
        if (StringUtil.isNotEmpty(userName) && !"undefined".equals(userName) ) {
            sql += " and u.nickname like '%"+userName+"%' ";
        }
	    
        return jdbcTemplate.queryForInt(sql);
    }

    @Override
    public List getAllUser(String userName, String isAuth,String sex,String userRole,int pageNo,int pageSize) {
        int beginRow = (pageNo - 1)* pageSize;
        String sql = " select u.user_id,"
            + " u.user_name,"
            + " u.nickname,"
            + " u.real_name,"
            + " u.avatar,"
            + " u.sex,"
            + " u.is_auth,"
            + " s.live_province,"
            + " s.live_city,"
            + " IFNULL(v.type,0) type"
            + " from t_user u "
            + " left join t_user_secretary s on s.user_id = u.user_id"
            + " left join t_authentication v on v.user_id = u.user_id "
            + " where 1 = 1 and u.if_del = '0' ";
        
        if (StringUtil.isNotEmpty(userRole)) {
            sql += " and u.user_role = '"+userRole+"' ";
        }
        
        //性别
        if (StringUtil.isNotEmpty(sex)) {
            sql += " and u.sex = '"+sex+"' ";
        }

        //认证
        if (StringUtil.isNotEmpty(isAuth)) {
            sql += " and v.type = "+isAuth;
        }
        
        //用户名查询
        if (StringUtil.isNotEmpty(userName) && !"undefined".equals(userName)) {
            sql += " and u.nickname like '%"+userName+"%' ";
        }
        
        sql += " order by u.created_time DESC ";
        sql += " LIMIT "+ String.valueOf(beginRow)+ "," + pageSize;
        
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public UserEntityVo getUserId(String userId) {
        String sql = " select u.user_id,"
            + " u.user_name,"
            + " u.user_pwd,"
            + " u.mobile,"
            + " u.nickname,"
            + " u.avatar,"
            + " u.real_name,"
            + " u.sex,"
            + " u.is_auth,"
            + " u.job_age,"
            + " s.live_province,"
            + " s.sec_height,"
            + " s.education,"
            + " s.specialty,"
            + " s.job,"
            + " s.tag_ids,"
            + " s.money_day,"
            + " s.job_area,"
            + " s.nationality,"
            + " s.nation,"
            + " s.blood_style,"
            + " s.live_province,"
            + " s.live_city,"
            + " s.home_province,"
            + " s.home_city,"
            + " s.school,"
            + " s.intro,"
            + " s.sec_front_photo,"
            + " s.sec_full_photo,"
            + " s.sec_live_photo,"
            + " s.created_time,"
            + " v.video"
            + " from t_user u "
            + " left join t_user_secretary s on s.user_id = u.user_id "
            + " left join t_secretary_video v on v.user_id = u.user_id"
            + " where 1 = 1 and u.if_del = '0' ";
       
        if (StringUtil.isNotEmpty(userId)) {
            sql += " and u.user_id = " + userId;
        }
        
        UserEntityVo userEntityVo = new UserEntityVo();
        
        jdbcTemplate.query(sql, new RowMapper<UserEntityVo>() {
            public UserEntityVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                userEntityVo.setUserId(rs.getString("user_id"));
                //用户名
                userEntityVo.setUserName(rs.getString("user_name"));
                
                //用户昵称
                userEntityVo.setNickname(rs.getString("nickname"));
                //用户头像
                userEntityVo.setAvatar(rs.getString("avatar"));
                //手机号
                userEntityVo.setMobile(rs.getString("mobile"));
                //性别
                userEntityVo.setSex(rs.getString("sex"));
                //工作年限
                userEntityVo.setJobAge(rs.getString("job_age"));
                //真实名
                userEntityVo.setRealName(rs.getString("real_name"));
                //
                userEntityVo.setSecHeight(rs.getLong("sec_height"));
                userEntityVo.setEducation(rs.getString("education"));
                userEntityVo.setSpecialty(rs.getString("specialty"));
                userEntityVo.setTagIds(rs.getString("tag_ids"));
                userEntityVo.setJobArea(rs.getString("job_area"));
                userEntityVo.setNation(rs.getString("nation"));
                userEntityVo.setBloodStyle(rs.getString("blood_style"));
                userEntityVo.setLiveProvince(rs.getString("live_province"));
                userEntityVo.setHomeProvince(rs.getString("home_province"));
                userEntityVo.setSchool(rs.getString("school"));
                userEntityVo.setIntro(rs.getString("intro"));
                userEntityVo.setVideoId(rs.getString("video"));
                userEntityVo.setSecFrontPhoto(rs.getString("sec_front_photo"));
                userEntityVo.setSecFullPhoto(rs.getString("sec_full_photo"));
                userEntityVo.setSecLivePhoto(rs.getString("sec_live_photo"));
                userEntityVo.setJob(rs.getString("job"));
                
                return userEntityVo;
            }
        });
        
        return userEntityVo;
    }
    
    @Override
    public BossEntityVo getBossUserId(String userId){
        String sql = " select u.user_id,"
            + " u.user_name,"
            + " u.mobile,"
            + " u.nickname,"
            + " u.avatar,"
            + " u.real_name,"
            + " u.sex,"
            + " u.is_auth,"
            + " u.job_age,"
            
            + " b.company,"
            + " b.duty,"
            + " b.industry,"
            + " b.interest_tag,"
            + " b.boss_sign,"
            + " b.sec_front_photo,"
            + " b.sec_full_photo,"
            + " b.sec_live_photo,"
            + " v.video"
            + " from t_user u "
            + " left join t_user_boss b on b.user_id = u.user_id "
            + " left join t_secretary_video v on v.user_id = u.user_id"
            + " where 1 = 1 and u.if_del = '0' ";
        
        if (StringUtil.isNotEmpty(userId)) {
            sql += " and u.user_id = " + userId;
        }
        
        BossEntityVo bossEntityVo = new BossEntityVo();
        
        jdbcTemplate.query(sql, new RowMapper<BossEntityVo>() {
            public BossEntityVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                bossEntityVo.setUserId(rs.getLong("user_id"));
                //用户昵称
                bossEntityVo.setUserName(rs.getString("user_name"));
                //用户头像
                bossEntityVo.setAvatar(rs.getString("avatar"));
                //手机号
                bossEntityVo.setMobile(rs.getString("mobile"));
                //性别
                bossEntityVo.setSex(rs.getString("sex"));
                //工作年限
                bossEntityVo.setJobAge(rs.getString("job_age"));
                //真实名
                bossEntityVo.setRealName(rs.getString("real_name"));
                //视频
                bossEntityVo.setVideo(rs.getString("video"));
                //boss 基本信息
                bossEntityVo.setCompany(rs.getString("company"));
                bossEntityVo.setDuty(rs.getString("duty"));
                bossEntityVo.setIndustry(rs.getString("industry"));
                bossEntityVo.setInterestTag(rs.getString("interest_tag"));
                bossEntityVo.setBossSign(rs.getString("boss_sign"));
                bossEntityVo.setSecFrontPhoto(rs.getString("sec_front_photo"));
                bossEntityVo.setSecFullPhoto(rs.getString("sec_full_photo"));
                bossEntityVo.setSecLivePhoto(rs.getString("sec_live_photo"));
                
                return bossEntityVo;
            }
        });
        return bossEntityVo;
    }

    @Override
    public int deleteUserId(String userId) {
        String sql = "update t_user set if_del = '1' where user_id = " +userId;
        return jdbcTemplate.update(sql);
    }

    @Override
    public int coutAuditing(String userRole) {
        String sql = "select count(*) from t_user u "
                + " left join t_authentication v on v.user_id = u.user_id where v.type = '1' and u.if_del = '0' ";
        
        if (StringUtil.isNotEmpty(userRole)) {
            sql += " and v.type = '"+userRole+"' ";
        }
        
        return jdbcTemplate.queryForInt(sql);
    }

	@Override
	public long updateAuthVerify(UserAuthVerifyEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getvRealName());
		l.add(entity.getvFrontCard());
		l.add(entity.getvBackCard());
		l.add(entity.getvVideo());
		l.add(entity.getUserId());
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_USER_VERIFY), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public List<HeightEntity> getAllHeight() {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();

		List<HeightEntity> list = new ArrayList<HeightEntity>();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_ALL_HEIGHT), l, dataSource);
			while(dbHandler.rs.next()){

				HeightEntity entity = new HeightEntity();
				entity.setId(dbHandler.rs.getLong("id"));
				entity.setName(dbHandler.rs.getString("name"));
				entity.setPrice(dbHandler.rs.getDouble("price"));
				list.add(entity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public List<EducationEntity> getAllEducation() {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();

		List<EducationEntity> list = new ArrayList<EducationEntity>();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_ALL_EDUCATION), l, dataSource);
			while(dbHandler.rs.next()){

				EducationEntity entity = new EducationEntity();
				entity.setId(dbHandler.rs.getLong("id"));
				entity.setName(dbHandler.rs.getString("name"));
				entity.setPrice(dbHandler.rs.getDouble("price"));
				list.add(entity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public List<OccupationEntity> getAllOccupation() {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();

		List<OccupationEntity> list = new ArrayList<OccupationEntity>();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_ALL_OCCUPATION), l, dataSource);
			while(dbHandler.rs.next()){

				OccupationEntity entity = new OccupationEntity();
				entity.setId(dbHandler.rs.getLong("id"));
				entity.setName(dbHandler.rs.getString("name"));
				entity.setPrice(dbHandler.rs.getDouble("price"));
				list.add(entity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public List<SecretaryEntity> getAllOneSpecialty() {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();

		List<SecretaryEntity> list = new ArrayList<SecretaryEntity>();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_ALL_ONE_SPECIALTY), l, dataSource);
			while(dbHandler.rs.next()){

				SecretaryEntity entity = new SecretaryEntity();
				entity.setId(dbHandler.rs.getLong("id"));
				entity.setName(dbHandler.rs.getString("name"));
				entity.setLevel(dbHandler.rs.getString("level"));
				list.add(entity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public List<SecretaryEntity> getAllTwoSpecialty(String parent) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(parent);
		
		List<SecretaryEntity> list = new ArrayList<SecretaryEntity>();
		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.SELECT_ALL_TWO_SPECIALTY), l, dataSource);
			while(dbHandler.rs.next()){

				SecretaryEntity entity = new SecretaryEntity();
				entity.setId(dbHandler.rs.getLong("id"));
				entity.setName(dbHandler.rs.getString("name"));
				entity.setPrice(dbHandler.rs.getDouble("price"));
				list.add(entity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

    @Override
    public int getListBossShCount(String nickname) {
        String sql = "select count(*) from t_boss_schedule a"
            + " left join t_user u on u.user_id = a.user_id "
            + " where 1 = 1 and a.if_del = '0' ";
        if (StringUtil.isNotEmpty(nickname)) {
            sql += " and u.nickname like '%"+nickname+"%' ";
        }
        
        return jdbcTemplate.queryForInt(sql);
    }

    @Override
    public List getAllBossSh(String nickname, int pageNo, int pageSize) {

        int beginRow = (pageNo - 1)* pageSize;
        String sql = "select a.bs_id,"
                + " a.created_time,"
                + " u.nickname "
                + " from t_boss_schedule a"
                + " left join t_user u on u.user_id = a.user_id "
                + " where 1 = 1 and a.if_del = '0' ";
            
            if (StringUtil.isNotEmpty(nickname)) {
                sql += " and u.nickname like '%"+nickname+"%' ";
            }
            
            sql += " order by a.created_time DESC ";
            sql += " LIMIT "+ String.valueOf(beginRow)+ "," + pageSize;
            
            return jdbcTemplate.queryForList(sql);
    }

    @Override
    public int deleteBossShId(String userId) {
        
        String sql = "update t_boss_schedule set if_del = '1' where bs_id = " +userId;
        return jdbcTemplate.update(sql);
    }

    @Override
    public UserBossScheduleEntity getBossSh(String userId) {
        String sql = "select a.bs_id,"
            + " a.user_id,"
            + " a.schedule_type,"
            + " a.start_time,"
            + " a.end_time,"
            + " a.target_province,"
            + " a.target_city,"
            + " a.people_number,"
            + " a.days,"
            + " a.money_day,"
            + " a.demand,"
            + " a.created_time "
            + " from t_boss_schedule a"
            + " left join t_user u on u.user_id = a.user_id "
            + " where 1 = 1 and a.if_del = '0' ";
        
        if (StringUtil.isNotEmpty(userId)) {
            sql += " and a.bs_id = " + userId;
        }
        
        UserBossScheduleEntity scheduleEntity = new UserBossScheduleEntity();
        jdbcTemplate.query(sql, new RowMapper<UserBossScheduleEntity>() {
            public UserBossScheduleEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                scheduleEntity.setUserId(rs.getLong("user_id"));
                scheduleEntity.setScheduleType(rs.getString("schedule_type"));
                scheduleEntity.setStartTime(rs.getString("start_time"));
                scheduleEntity.setEndTime(rs.getString("end_time"));
                scheduleEntity.setTargetProvince(rs.getString("target_province"));
                scheduleEntity.setTargetCity(rs.getString("target_city"));
                scheduleEntity.setPeopleNumber(rs.getString("people_number"));
                scheduleEntity.setDays(rs.getString("days"));
                scheduleEntity.setMoneyDay(rs.getDouble("money_day"));
                scheduleEntity.setDemand(rs.getString("demand"));
                scheduleEntity.setCreateTime(rs.getString("created_time"));
                
                return scheduleEntity;
            }
        });
        
        return scheduleEntity;
    }

    @Override
    public int getListVerifyCount(String mobile, String userRole, String sex) {
        String sql = "select count(*) from t_user_verify a"
                + " left join t_user u on u.user_id = a.user_id "
                + " where 1 = 1 and u.if_del = '0' ";
            if (StringUtil.isNotEmpty(mobile)) {
                sql += " and u.mobile like '%"+mobile+"%' ";
            }
            
            if (StringUtil.isNotEmpty(userRole)) {
                sql += " and u.user_role = '"+userRole+"' ";
            }
            
            if (StringUtil.isNotEmpty(sex)) {
                sql += " and u.sex = '"+sex+"' ";
            }
            
            return jdbcTemplate.queryForInt(sql);
    }

    @Override
    public List getListAllVerify(String mobile, String userRole, String sex,
            int pageNo, int pageSize) {
        
        int beginRow = (pageNo - 1)* pageSize;
        String sql = "select a.verify_id,"
                + " a.v_type,"
                + " a.v_real_name,"
                + " a.created_time,"
                + " u.mobile,"
                + " u.user_role,"
                + " u.sex"
                + " from t_user_verify a"
                + " left join t_user u on u.user_id = a.user_id "
                + " where 1 = 1 and u.if_del = '0' ";
            if (StringUtil.isNotEmpty(mobile)) {
                sql += " and u.mobile like '%"+mobile+"%' ";
            }
            
            if (StringUtil.isNotEmpty(userRole)) {
                sql += " and u.user_role = '"+userRole+"' ";
            }
            
            if (StringUtil.isNotEmpty(sex)) {
                sql += " and u.sex = '"+sex+"' ";
            }
            
            sql += " order by a.created_time DESC ";
            sql += " LIMIT "+ String.valueOf(beginRow)+ "," + pageSize;
            
            return jdbcTemplate.queryForList(sql);
    }

    @Override
    public UserVerifyEntityVo getVerifyId(String id) {
        String sql = "select a.verify_id,"
                + " a.v_real_name,"
                + " a.v_front_card,"
                + " a.v_back_card,"
                + " a.v_video,"
                + " a.v_type,"
                + " a.created_time,"
                + " a.v_remind "
                + " from t_user_verify a "
                + " left join t_user u on u.user_id = a.user_id "
                + " where 1 = 1 and a.if_del = '0' ";
        
            if (StringUtil.isNotEmpty(id)) {
                sql += " and a.verify_id = " + id;
            }
            
            UserVerifyEntityVo verifyEntityVo = new UserVerifyEntityVo();
            jdbcTemplate.query(sql, new RowMapper<UserVerifyEntityVo>() {
                public UserVerifyEntityVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                    verifyEntityVo.setId(rs.getLong("verify_id"));
                    verifyEntityVo.setvRealName(rs.getString("v_real_name"));
                    verifyEntityVo.setvFrontCard(rs.getString("v_front_card"));
                    verifyEntityVo.setvBackCard(rs.getString("v_back_card"));
                    verifyEntityVo.setvVideo(rs.getString("v_video"));
                    verifyEntityVo.setCreatedTime(rs.getString("created_time"));
                    verifyEntityVo.setvRemind(rs.getString("v_remind"));
                    verifyEntityVo.setvType(rs.getInt("v_type"));
                    
                    return verifyEntityVo;
                }
            });
            return verifyEntityVo;
    }

    @Override
    public int updateVType(String vType,String id,String describe) {

        String sql = "update t_user_verify set v_type = '"+vType+"' ,v_remind = '"+describe+"' where verify_id = " +id;
        return jdbcTemplate.update(sql);
    }

    @Override
	public List<BannerEntity> getBannerByType(BannerEntity entity) {
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getPosition());
		
		DBHandler dbHandler = new DBHandler();
		
		List<BannerEntity> list = new ArrayList<BannerEntity>();
		try {
			dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.SELECT_GET_BANNER_BYTYPE), l, dataSource);
			while(dbHandler.rs.next()){

				BannerEntity rsEntity = BannerEntity.getInstance(dbHandler.rs);

				list.add(rsEntity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long addCashOut(UserCashEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getUserId());
		l.add(entity.getAccountBalance());
		l.add(entity.getCashBalance());
		l.add(1);
		l.add(DateUtil.getNowTimeUnFormat() + Util.get6code());
		l.add(entity.getCardNumber());
		l.add(DateUtil.getSysNowTime());
		l.add(entity.getBranchAccount());
		l.add(entity.getRealName());
		l.add(entity.getBank());
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.ADD_USER_CASH_OUT), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public long updateUserBalance(double cashOut, long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(cashOut);
		l.add(userId);
		
		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_USER_BALANCE), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public List<UserCashEntity> getUserCashDetail(long userId) {
		List<Object> l = new ArrayList<Object>();
		l.add(userId);
		
		DBHandler dbHandler = new DBHandler();
		
		List<UserCashEntity> list = new ArrayList<UserCashEntity>();
		try {
			dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.SELECT_USER_CASH_DETAIL), l, dataSource);
			while(dbHandler.rs.next()){

				UserCashEntity rsEntity = UserCashEntity.getInstance(dbHandler.rs);

				list.add(rsEntity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long addMoneyLog(UserMoneyLogEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getUserId());
		l.add(entity.getHappenMoney());
		l.add(entity.getDescription());

		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.ADD_MONEY_LOG), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public PhoneVersionEntity getPhoneVersionByType(int type) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(type);

		try {
			dbHandler.rs= dbHandler.query(DBUtil.getSql(Constants.GET_PHOME_VERSION_BYTYPE), l, dataSource);
			if(dbHandler.rs.next()){

				PhoneVersionEntity entity = PhoneVersionEntity.getInstance(dbHandler.rs);
				
				return entity;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long reportScreenshot(ReportEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getUserId());
		l.add(entity.getContent());
		l.add(entity.getImgUrl());

		long i = 0;
		try {
			i = dbHandler.update(DBUtil.getSql(Constants.ADD_REPORT_SCREENSHOT), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@SuppressWarnings("unchecked")
	@Override
	public UserSecScheduleEntity getUserSecScheduleId(String ssId) {
		
		String sql = "select IFNULL(a.ss_id,0) ss_id,"
				+ " IFNULL(a.user_id,0) user_id,"
				+ " a.cover_url,"
				+ " a.title,"
				+ " a.shop_mark,"
				+ " a.shop_province,"
				+ " a.shop_city,"
				+ " a.shop_district,"
				+ " a.shop_address,"
				+ " IFNULL(a.point_number,0) point_number,"
				+ " IFNULL(a.shop_latitude,0) shop_latitude,"
				+ " IFNULL(a.shop_longitude,0) shop_longitude,"
				+ " a.tag,"
				+ " a.ss_content,"
				+ " IFNULL(a.read_count,0) read_count,"
				+ " a.created_time,"
				+ " u.avatar,"
				+ " u.nickname,"
				+ " IFNULL(avg(oc.grade),0) grade,"
				+ " IFNULL(us.money_day,0) money,"
				+ " m.mer_logo image"
				+ " from t_secretary_schedule a "
				+ " left join t_user u on u.user_id = a.user_id "
				+ " left join t_order_comment oc on a.user_id = oc.receive_user_id "
				+ " left join t_user_secretary us on a.user_id = us.user_id "
				+ " left join t_merchant m on m.shop_id = a.shop_id "
				+ " where 1 = 1 and a.if_del = '0' ";
        
            sql += " and a.ss_id = '"+ssId+"' ";
        
        UserSecScheduleEntity entity = new UserSecScheduleEntity();
        try {
        	entity = (UserSecScheduleEntity) jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(UserSecScheduleEntity.class));
		} catch (DataAccessException e) {
			
		}
        
        return entity;
	}

	@Override
	public int updateOrdersState(long userId,int ordersState) {
		
		String sql = "update t_user set orders_state = "+ordersState+" where user_id = " + userId;
		
		return jdbcTemplate.update(sql);
	}

	@Override
	public int updateCommentCountPlus(long ssId) {
		
		String sql = "update t_secretary_schedule set comment_count = comment_count +1 where ss_id = "+ssId;
		
		return jdbcTemplate.update(sql);
	}

	@Override
	public int updateCommentCountReduce(long ssId) {

		String sql = "update t_secretary_schedule set comment_count = comment_count -1 where ss_id = "+ssId;
		
		return jdbcTemplate.update(sql);
	}

	@Override
	public int updateTransactionNumber(long userId) {
		
		String sql = "update t_user_secretary set transaction_number = transaction_number +1 where user_id = "+userId;
		
		return jdbcTemplate.update(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<UserSecScheduleEntity> listRanking(String stateTime,String endTime, int pageNo,int pageSize) {
		
		int beginRow = (pageNo - 1)* pageSize;
		
		String sql = "select ss.*,"
				+ " u.avatar,"
				+ " u.nickname,"
				+ " u.orders_state,"
				+ " us.money_day "
				+ " from t_secretary_schedule ss "
				+ " left join t_user u on ss.user_id = u.user_id "
				+ " left join t_user_secretary us on ss.user_id = us.user_id "
				+ " where 1 = 1 and ss.if_del = 0 ";
			
			if (StringUtil.isNotEmpty(stateTime)) {
				sql += " and ss.created_time >='" +stateTime+"'";
			}
			
			if (StringUtil.isNotEmpty(endTime)) {
				sql += " and ss.created_time <='" +endTime+"'";
			}
		
			sql += " order by ss.point_number DESC ";
	        sql += " LIMIT "+ String.valueOf(beginRow)+ "," + pageSize;
	        
	        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(UserSecScheduleEntity.class));
	}

	@Override
	public int updatePoint(long ssId) {
		
		String sql = "update t_secretary_schedule set point_number = point_number +1 where ss_id = "+ssId;
		
		return jdbcTemplate.update(sql);
	}

	@Override
	public int getPointCount(long ssId, long userId) {
		
		String sql = "select count(*) from t_point where ss_id = " + ssId + " and user_id =" + userId;
		
		return jdbcTemplate.queryForInt(sql);
	}

	@Override
	public int savePoint(PointEntity pointEntity) {
		
		String sql = "insert into t_point(user_id,ss_id) values(?,?)";
		
        return jdbcTemplate.update(sql,new Object[]{pointEntity.getUserId(),pointEntity.getSsId()});
	}


}
